global data_folder "W:\intimate\data"
global log_folder "W:\intimate\dofiles\logs"
global result_folder "W:\intimate\results_revision"

***********************SHELTER DATAT*****************************************

import excel "W:\shelter_vote_data\kunta_1_20050101_mapping.xlsx", sheet("Koodit ja nimet") firstrow clear
rena luokannimi Municipality
renam luokankoodi kunta
keep Municipality kunta
duplicates drop
sort Municipality
save "$data_folder\munic_names", replace


use "W:\shelter_vote_data\vainturvakodit.dta", clear
sort Municipality
*note, two locations named wrongly
replace Municipality="Kajaani" if Municipality=="Kainuu"
replace Municipality="Karjaa" if Municipality=="Raasepori"
*ren Raasepori Karjaa
sort Municipality

merge Municipality using "munic_names" , nokeep
rename Year year
list Municipality if kunta==""
count if kunta==""
tab Municipality if kunta==""
drop if kunta==""
sort kunta year
sum shelternum familyrooms spentdays client
save "$data_folder\shelters_mun", replace


*****DATA ON SOCIAL WORKERS AT MUNICIPALITY LEVEL***************************************

use shnro vuosi ammatti using "D:\ready-made\FOLK_tkt_11a\folk_20112018_tua_tkt21tot_1.dta", clear
sort shnro vuosi
merge 1:1 shnro vuosi using   "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus22tot_1.dta", keepusing(kunta) update
drop if _merge==2
drop _merge
*erityisasiantuntijat
gen social_worker_high=ammatti=="26351"
*broader measure
gen social_worker_high_broad=ammatti=="2635" | ammatti=="26351"
*asiantuntijat
*erityisasiantuntijat
gen social_worker_low=ammatti=="34121"
*broader measure
gen social_worker_low_broad=ammatti=="3412" | ammatti=="34121"


gen social_worker_any=ammatti=="2635" | ammatti=="26351" | ammatti=="3412" | ammatti=="34121"
gen n=1
drop if kunta==""
collapse (sum) n (mean) social_* , by(kunta vuosi)
sort vuosi kunta
save  $data_folder\socialworkers20112020, replace


*******************SOCIAL WORKER DATA- EARLIER YEARS 
use shnro vuosi ammatti using "D:\ready-made\FOLK_tkt_0110a\folk_20012010_tua_tkt21tot_1.dta", clear
sort shnro vuosi
merge 1:1 shnro vuosi using   "D:\ready-made\FOLK_perus_0110a\folk_20012010_tua_perus21tot_1.dta", keepusing(kunta) update
drop if _merge==2
drop _merge
*erityisasiantuntijat
gen social_worker_high=ammatti=="26351"
*broader measure
gen social_worker_high_broad=ammatti=="2635" | ammatti=="26351"
*asiantuntijat
*erityisasiantuntijat
gen social_worker_low=ammatti=="34121"
*broader measure
gen social_worker_low_broad=ammatti=="3412" | ammatti=="34121"
gen social_worker_any=ammatti=="2635" | ammatti=="26351" | ammatti=="3412" | ammatti=="34121"
gen n=1
collapse (sum) n (mean) social_* , by(kunta vuosi)
drop if kunta==""
tab vuosi
sort vuosi kunta
append using  $data_folder\socialworkers20112020
rename vuosi year
sort kunta year
save  $data_folder\socialworkers, replace


********************INDIVIDUAL LEVEL DATA CONSTRUCTION******************


use "W:\intimate\data\match_victim_data_clean_new", clear
sum match_id
drop year_event


// change into long format
local varlist sphnro ptoim1 saiprva tyotu nchild info_missing same_spouse seutukunta16 kunta 

g same_spouse0 = 1
forvalues i = 1/5 {
	g same_spouseB`i' = (sphnro0 == sphnroB`i')
}
forvalues i = 1/5 {
	g same_spouseF`i' = (sphnro0 == sphnroF`i')
}

forvalues i = 1/5 {
    local j = 6 -`i'
    foreach thing in `varlist' {
		ren `thing'B`i' `thing'`j'
	}
}

foreach thing in `varlist' {
    ren `thing'0 `thing'6
}

forvalues i = 1/5 {
    local j = 6 +`i'
    foreach thing in `varlist' {
		ren `thing'F`i' `thing'`j'
	}
}

g group_id = _n

reshape long `varlist', i(group_id) j(time)


// merge in outside options
rename educ level 
gen educ=1 if level<3
replace educ=2 if leve>=3 & level<=5
replace educ=3 if level>=6 
 


replace age=floor(age/5)


g year_event = year_start_cohab-1
drop year
g year = year_start_cohab + (time - 6)
gen treat= dv_couple==1
g time_ = time-6
tab year

************NOTE, THE REGION VARIABLE IS MISSING FOR THE LATER YEARS (2017-)***********************
***********WE MERGE THE REGIONAL CODES FROM FOLK TO OUR DATA***************
rename year vuosi
rename kunta mun_old
sort shnro vuosi
merge m:1 shnro vuosi using   "D:\ready-made\FOLK_perus_0110a\folk_20012010_tua_perus21tot_1.dta", keepusing(kunta)
drop if _merge==2
drop _merge
sort shnro vuosi
merge m:1 shnro vuosi using   "D:\ready-made\FOLK_perus_11a\folk_20112020_tua_perus22tot_1.dta", keepusing(kunta) update
drop if _merge==2
drop _merge
rename vuosi year
tab year
*MERGE IN THE SOCIAL WORKERS DATA*
sort kunta year
merge m:1 kunta year using  $data_folder\socialworkers
tab _merge
drop if _merge==2
tab year if _merge==3
drop _merge

*MERGE IN THE SHELTER MEASURE
sort kunta year
merge m:1 kunta year using  "$data_folder\shelters_mun", keepusing(shelternum familyrooms spentdays)
drop if _merge==2
tab year if _merge==3
gen sheltermissing=_merge==3
drop  _merge

replace same_spouse=0 if same_spouse==. 

*Time displacement dummies
g dpl_5=time_==-5 & treat==1
g dpl_4=time_==-4 & treat==1
g dpl_3=time_==-3 & treat==1
g dpl_2=time_==-2 & treat==1
g dpl_1=time_==-1 & treat==1
g dpl_0=time_==0 & treat==1
gen dpl1=time_==1 & treat==1
gen dpl2=time_==2 & treat==1
gen dpl3=time_==3 & treat==1
gen dpl4=time_==4 & treat==1
gen dpl5=time_==5 & treat==1

gen treatPost= treat==1 & time_>=0
egen full_fe = group(match_id time)
egen munic_id = group(kunta)

cap drop breakup 
gen breakup=1-same_spouse
replace shelternum=0 if shelternum==.
gen anyshelter=shelternum!=0 
tab anyshelter

tab year if anyshelter==1
gen anyshelter_dv=anyshelter*dv_couple 
gen shelternum_dv=shelternum*dv_couple 

replace social_worker_any=social_worker_any*1000
gen social_worker_any_dv=social_worker_any*dv_couple 
bysort dv_couple: tab anyshelter_dv 
bysort time: sum breakup
replace breakup=0 if time<=5 


label var anyshelter "Shelter"
label var anyshelter_dv "Shelter x DV"
label var dv_couple "DV"

label var shelternum "# of shelters"
label var shelternum_dv "# of shelters  x DV"

label var social_worker_any "Social workers"
label var social_worker_any_dv "Social workers x DV"

**********REGRESSION TABLES
global fe = "full_fe year kunta" 
global cluster = "match_id"

reghdfe breakup dv_couple social_worker_any social_worker_any_dv if time>=5,  absorb($fe)  cluster($cluster)		
est sto breakup_anysh

su breakup if e(sample)==1
local depmean = round(r(mean), 0.001)
estadd local depmean "`depmean'"

reghdfe breakup dv_couple shelternum shelternum_dv if time>=5,  absorb($fe)  cluster($cluster)		
est sto breakup_nsh

su breakup if e(sample)==1
local depmean = round(r(mean), 0.001)
estadd local depmean "`depmean'"

esttab breakup_anysh  breakup_nsh ///
			using "$result_folder\socialworkers_shelters.tex", replace keep(dv_couple shelternum shelternum_dv social_worker_any social_worker_any_dv) scalars("depmean Dependent Mean")

		


	